﻿/*========================== 
 * @author 郑金泉
 * @desc OLE数据访问类
 * ========================= */
using System;
using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Collections;
using System.Configuration;
using System.Text;
using System.Web;

using Obsidian.Utils;
using Obsidian.Data.Sql;

namespace Obsidian.Data.Sql.OleDbClient
{
    public class OleDbExecutor
    {

        #region #

        private static Hashtable parametersCache = Hashtable.Synchronized(new Hashtable());

        private string _connectionString = null;

        public OleDbExecutor() { }

        public OleDbExecutor(string connectionString)
        {
            this._connectionString = connectionString;
        }

        /// <summary>
        /// 设置Command对象
        /// </summary>
        /// <param name="commandType">执行类型</param>
        /// <param name="commandText">SQL文本</param>
        /// <param name="transaction">事务</param>
        private void PrepareCommand(OleDbCommand command, CommandType commandType, string commandText, OleDbParameter[] oleDbParams, OleDbTransaction transaction)
        {
            command.CommandType = commandType;

            if (String.IsNullOrEmpty(commandText))
                throw new ArgumentNullException("commandText");
            else
                command.CommandText = commandText;

            if (oleDbParams != null)
            {
                foreach (OleDbParameter p in oleDbParams)
                {
                    command.Parameters.Add(p);
                }
            }

            if (transaction != null)
                command.Transaction = transaction;

            OleDbConnection connection = command.Connection;
            connection.ConnectionString = this._connectionString;
            if (connection.State != ConnectionState.Open)
                connection.Open();
        }

        public string ConnectionString
        {
            get { return this._connectionString; }
            set { this._connectionString = value; }
        }

        #endregion

        #region 参数缓存

        /// <summary>
        /// 重新得到缓存中的参数
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public static OleDbParameter[] GetCacheParameters(string key)
        {
            OleDbParameter[] cachedParams = (OleDbParameter[])parametersCache[key];

            if (cachedParams == null)
                return null;

            OleDbParameter[] clonedParams = new OleDbParameter[cachedParams.Length];

            for (int i = 0, j = clonedParams.Length; i < j; i++)
                clonedParams[i] = (OleDbParameter)((ICloneable)cachedParams[i]).Clone();

            return clonedParams;
        }

        /// <summary>
        /// 设置参数数组到缓存
        /// </summary>
        /// <param name="key"></param>
        /// <param name="oleDbParams"></param>
        public static void SetCacheParameters(string key, OleDbParameter[] oleDbParams)
        {
            if (key == null || String.IsNullOrEmpty(key))
                throw new ArgumentNullException("key");
            if (oleDbParams == null)
                throw new ArgumentNullException("oleDbParams");

            parametersCache[key] = oleDbParams;
        }

        #endregion

        #region 执行并返回受影响的行数



        /// <summary>
        /// 执行并返回受影响的行数
        /// </summary>
        /// <returns>受影响行数</returns>
        public int ExecuteNonQuery(string commandText)
        {
            return SqlExecuteNonQuery(CommandType.Text, commandText, null);
        }


        /// <summary>
        /// 执行并返回受影响的行数
        /// </summary>
        /// <returns>受影响行数</returns>
        public int ExecuteNonQuery(CommandType commandType, string commandText)
        {
            return SqlExecuteNonQuery(commandType, commandText, null);
        }

        /// <summary>
        /// 执行并返回受影响的行数
        /// </summary>
        /// <returns>受影响行数</returns>
        public int ExecuteNonQuery(string commandText, OleDbParameter[] oleDbParams)
        {
            return SqlExecuteNonQuery(CommandType.Text, commandText, oleDbParams);
        }

        /// <summary>
        /// 执行并返回受影响的行数
        /// </summary>
        /// <returns>受影响行数</returns>
        public int ExecuteNonQuery(CommandType commandType, string commandText, OleDbParameter[] oleDbParams)
        {
            return SqlExecuteNonQuery(commandType, commandText, oleDbParams);
        }

        /// <summary>
        /// 执行并返回受影响的行数
        /// </summary>
        /// <returns>受影响行数</returns>
        private int SqlExecuteNonQuery(CommandType commandType, string commandText, OleDbParameter[] oleDbParams)
        {
            int n;
            using (OleDbConnection connection = new OleDbConnection(this._connectionString))
            {
                OleDbCommand command = connection.CreateCommand();
                PrepareCommand(command, commandType, commandText, oleDbParams, null);
                try { n = command.ExecuteNonQuery(); }
                catch { throw; }
                finally
                {
                    command.Parameters.Clear();
                    command.Dispose();
                    connection.Close();
                    connection.Dispose();
                }
            }
            return n;
        }

        #endregion

        #region 执行并返回结果集中第一行第一列的执行结果，忽略额外的列或行



        /// <summary>
        /// 执行并返回结果集中第一行第一列的执行结果，忽略额外的列或行
        /// </summary>
        /// <returns>结果集中第一行第一列的执行结果，忽略的列或行</returns>
        public object ExecuteScalar(string commandText)
        {
            return SqlExecuteScalar(CommandType.Text, commandText, null);
        }

        /// <summary>
        /// 执行并返回结果集中第一行第一列的执行结果，忽略额外的列或行
        /// </summary>
        /// <returns>结果集中第一行第一列的执行结果，忽略的列或行</returns>
        public object ExecuteScalar(CommandType commandType, string commandText)
        {
            return SqlExecuteScalar(commandType, commandText, null);
        }

        /// <summary>
        /// 执行并返回结果集中第一行第一列的执行结果，忽略额外的列或行
        /// </summary>
        /// <returns>结果集中第一行第一列的执行结果，忽略的列或行</returns>
        public object ExecuteScalar(string commandText, OleDbParameter[] oleDbParams)
        {
            return SqlExecuteScalar(CommandType.Text, commandText, oleDbParams);
        }

        /// <summary>
        /// 执行并返回结果集中第一行第一列的执行结果，忽略额外的列或行
        /// </summary>
        /// <returns>结果集中第一行第一列的执行结果，忽略的列或行</returns>
        public object ExecuteScalar(CommandType commandType, string commandText, OleDbParameter[] oleDbParams)
        {
            return SqlExecuteScalar(commandType, commandText, oleDbParams);
        }

        /// <summary>
        /// 执行并返回结果集中第一行第一列的执行结果，忽略额外的列或行
        /// </summary>
        /// <returns>结果集中第一行第一列的执行结果，忽略的列或行</returns>
        private object SqlExecuteScalar(CommandType commandType, string commandText, OleDbParameter[] oleDbParams)
        {
            object obj;
            using (OleDbConnection connection = new OleDbConnection(this._connectionString))
            {
                OleDbCommand command = connection.CreateCommand();
                PrepareCommand(command, commandType, commandText, oleDbParams, null);
                try { obj = command.ExecuteScalar(); }
                catch { throw; }
                finally
                {
                    command.Parameters.Clear();
                    command.Dispose();
                    connection.Close();
                    connection.Dispose();
                }
            }
            return obj;
        }


        #endregion

        #region 执行并返回DataSet

        /// <summary>
        /// 执行并返回DataSet
        /// </summary>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string commandText)
        {
            return SqlExecuteDataSet(CommandType.Text, commandText, null);
        }

        /// <summary>
        /// 执行并返回DataSet
        /// </summary>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(CommandType commandType, string commandText)
        {
            return SqlExecuteDataSet(commandType, commandText, null);
        }


        /// <summary>
        /// 执行并返回DataSet
        /// </summary>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string commandText, OleDbParameter[] oleDbParams)
        {
            return SqlExecuteDataSet(CommandType.Text, commandText, oleDbParams);
        }

        /// <summary>
        /// 执行并返回DataSet
        /// </summary>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(CommandType commandType, string commandText, OleDbParameter[] oleDbParams)
        {
            return SqlExecuteDataSet(commandType, commandText, oleDbParams);
        }

        /// <summary>
        /// 执行并返回DataSet
        /// </summary>
        /// <returns>DataSet</returns>
        private DataSet SqlExecuteDataSet(CommandType commandType, string commandText, OleDbParameter[] oleDbParams)
        {
            DataSet dataSet = new DataSet();
            using (OleDbConnection connection = new OleDbConnection(this._connectionString))
            {
                OleDbCommand command = connection.CreateCommand();
                PrepareCommand(command, commandType, commandText, oleDbParams, null);
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command);
                try { dataAdapter.Fill(dataSet); }
                catch
                {
                    dataSet.Dispose();
                    throw;
                }
                finally
                {
                    dataAdapter.Dispose();
                    command.Parameters.Clear();
                    command.Dispose();
                    connection.Close();
                    connection.Dispose();
                }
            }
            return dataSet;
        }

        #endregion

        #region 执行并返回DataReader

        /// <summary>
        /// 执行并返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        public OleDbDataReader ExecuteReader(string commandText)
        {
            return SqlExecuteReader(CommandType.Text, commandText, null, CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 执行并返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        public OleDbDataReader ExecuteReader(CommandType commandType, string commandText)
        {
            return SqlExecuteReader(commandType, commandText, null, CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 执行并返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        public OleDbDataReader ExecuteReader(string commandText, OleDbParameter[] oleDbParams)
        {
            return SqlExecuteReader(CommandType.Text, commandText, oleDbParams, CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 执行并返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        public OleDbDataReader ExecuteReader(CommandType commandType, string commandText, OleDbParameter[] oleDbParams)
        {
            return SqlExecuteReader(commandType, commandText, oleDbParams, CommandBehavior.CloseConnection);
        }


        /// <summary>
        /// 执行并返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        public OleDbDataReader ExecuteReader(string commandText, CommandBehavior behavior)
        {
            return SqlExecuteReader(CommandType.Text, commandText, null, behavior);
        }

        /// <summary>
        /// 执行并返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        public OleDbDataReader ExecuteReader(CommandType commandType, string commandText, CommandBehavior behavior)
        {
            return SqlExecuteReader(commandType, commandText, null, behavior);
        }

        /// <summary>
        /// 执行并返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        public OleDbDataReader ExecuteReader(string commandText, OleDbParameter[] oleDbParams, CommandBehavior behavior)
        {
            return SqlExecuteReader(CommandType.Text, commandText, oleDbParams, behavior);
        }

        /// <summary>
        /// 执行并返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        public OleDbDataReader ExecuteReader(CommandType commandType, string commandText, OleDbParameter[] oleDbParams, CommandBehavior behavior)
        {
            return SqlExecuteReader(commandType, commandText, oleDbParams, behavior);
        }

        /// <summary>
        /// 执行并返回DataReader
        /// </summary>
        /// <returns>DataReader</returns>
        private OleDbDataReader SqlExecuteReader(CommandType commandType, string commandText, OleDbParameter[] oleDbParams, CommandBehavior behavior)
        {
            OleDbDataReader dataReader;
            OleDbConnection connection = new OleDbConnection(this._connectionString);
            OleDbCommand command = connection.CreateCommand();
            PrepareCommand(command, commandType, commandText, oleDbParams, null);
            try { dataReader = command.ExecuteReader(behavior); }
            catch { throw; }
            finally
            {
                command.Parameters.Clear();
                command.Dispose();
            }
            return dataReader;
        }

        #endregion


        public OleDbCommand BeginTransaction(CommandType commandType, OleDbParameter[] oleDbParams, string commandText, out OleDbTransaction transaction)
        {
            OleDbConnection connection = new OleDbConnection(this._connectionString);
            OleDbCommand command = connection.CreateCommand();
            PrepareCommand(command, commandType, commandText, oleDbParams, null);
            transaction = connection.BeginTransaction();
            command.Transaction = transaction;
            return command;
        }

        #region 读取表结构
        /*
        public static DBTable GetTableSchema(string name)
        {
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                connection.Open();
                DataTable table = connection.GetSchema("columns", new string[] { null, null, name, null });
                if (table.Rows.Count == 0)
                    throw new Exception("找不到映射表 - " + name);
                List<DBField> fieldList = new List<DBField>();
                DBTable dbtable = new DBTable(name);
                foreach (DataRow r in table.Rows)
                {
                    string columnName = r[3].ToString();//列名
                    bool isPK = r[6].ToString() == "1";//主键,表的第一位要是主键
                    object defaultValue = r[8];//默认值
                    bool allowNull = r[10].ToString().ToLower() == "true";//是否允许为空
                    string type = r[11].ToString();//数据类型
                    int size = Convert.IsDBNull(r[13]) ? -1 : Convert.ToInt32(r[13]);//数据大小
                    bool isUNI = r[6].ToString() == "1";//是否是唯一约束
                    bool autoIncrement = r[6].ToString() == "1";//主键

                    DBField field = new DBField(
                        dbtable,
                        columnName,
                        isPK,
                        allowNull,
                        autoIncrement,
                        isUNI,
                        defaultValue,
                        ToDataType(type, size),
                        (size > 0 ? size : -1)
                        );
                    fieldList.Add(field);
                }
                dbtable.SetFields(fieldList.ToArray());
                connection.Close();
                return dbtable;
            }
        }

        private static DataType ToDataType(string type, int size)
        {
            switch (type.ToLower())
            {
                case "3":
                    return DataType.Int;
                case "130":
                    if (size > 0)
                        return DataType.VarChar;
                    else
                        return DataType.Text;
                case "7":
                    return DataType.DateTime;
                case "6":
                    return DataType.Decimal;
                case "11":
                    return DataType.Bit;
               case "28":
                    return DataType;
                default:
                    throw new Exception("找不到对应类型 - " + type);
            }
        }
        */
        #endregion
    
    }
}
